using System;
using System.Data;
using System.Data.OleDb;
using Geekees.App.Cashbook.Lib.Entity;
using Geekees.App.Cashbook.Lib.Helper;


namespace Geekees.App.Cashbook.Lib.DAL
{
	/// <summary>
	/// Summary description for DASecurity.
	/// </summary>
	public class DASecurity
	{
		public DASecurity()
		{
			
		}

		public static int AddUser( string userName, string displayName, string pwd )
		{
			if( IsUserExist( userName ) )
				throw new Exception( string.Format( "User {0} already exist!" , userName ) );

			int i = Consts.NO_ID;
			//string cDate = DateTime.Now.ToString( "yyyy-MM-dd HH:mm" );
			//string lDate = DateTime.Now.ToString( "yyyy-MM-dd HH:mm" );
			string sql = "INSERT INTO [User] "+
						"( Login, Pwd, CreationTime, LastLoginTime, Name, LastChangePasswordTime, Ticket ) VALUES " + 
						"( @Login,  @Pwd, @CreationTime, @LastLoginTime, @Name, @LastChangePasswordTime, @Ticket )";

			string sqlId= " SELECT MAX(Id) AS ThisID FROM [User]";

			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;;
			cmd.Parameters.Add( "@Pwd", OleDbType.VarChar ).Value = pwd;
			cmd.Parameters.Add( "@CreationTime", OleDbType.Date ).Value =  DateTime.Now;
			cmd.Parameters.Add( "@LastLoginTime", OleDbType.Date ).Value =  DateTime.Now;
			cmd.Parameters.Add( "@Name", OleDbType.VarChar ).Value = displayName;
			cmd.Parameters.Add( "@LastChangePasswordTime", OleDbType.Date ).Value = DateTime.Now;
			cmd.Parameters.Add( "@Ticket", OleDbType.VarChar ).Value = MD5Helper.GetMd5Sum( DateTime.Now.ToString() );//add lastchangepassword md5field as ticket

			OleDbCommand cmdId = new OleDbCommand( sqlId );

			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				h.ExecuteSQL( cmd );

				i = (int)h.ExecuteScalar( cmdId );
			}
			catch( Exception ex ){ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
			
			return i;
		}

		public static bool IsUserExist( string userName )
		{ 
			string sql = "select count(*) from [User] where [Login]=@Login";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;;
			
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				object o = h.ExecuteScalar( cmd );
				if( o == null )
					throw new Exception("IsUserExist select clause returns an null!!!");
				else
				{
					try
					{
						return ((int)o) > 0;
					}
					catch(Exception ex){
						throw new Exception("IsUserExist select clause returns a non-integer!" + ex.Message );
					}
				}

			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }
		}

		public static User LoginUser( string userName, string pwd )
		{
			string sql = "select * from [User] where [Login]=@Login";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;;
			SqlHelper h = new SqlHelper();
			User u = null;
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				if( dt == null || dt.Rows.Count == 0)
					return null;

				string userPwd = dt.Rows[0]["Pwd"].ToString();
				if ( userPwd != pwd )
					return null;

				u = new User();
				u.Id = int.Parse( dt.Rows[0]["Id"].ToString() );
				u.Login = dt.Rows[0]["Login"].ToString();
				u.Pwd = dt.Rows[0]["Pwd"].ToString();
				u.Name = dt.Rows[0]["Name"].ToString();
				u.CreationTime= DateTime.Parse( dt.Rows[0]["CreationTime"].ToString() );
				u.LastLoginTime = DateTime.Parse( dt.Rows[0]["LastLoginTime"].ToString());	
				u.LastChangePasswordTime = DateTime.Parse( dt.Rows[0]["LastChangePasswordTime"].ToString());		
				u.Ticket = dt.Rows[0]["Ticket"].ToString();
				u.TimeZone = TimeZoneHelper.GetTimeZone( int.Parse( dt.Rows[0]["TimeZone"].ToString() ) );
				
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

			return u;
		}

		

		public static User LoginUserUsingTicket( string userName, string ticket )
		{
			string sql = "select * from [User] where [Login]=@Login";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;;
			SqlHelper h = new SqlHelper();
			User u = null;
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				if( dt == null || dt.Rows.Count == 0)
					return null;

				string userTicket = dt.Rows[0]["Ticket"].ToString();
				if ( userTicket != ticket )
					return null;

				u = new User();
				u.Id = int.Parse( dt.Rows[0]["Id"].ToString() );
				u.Login = dt.Rows[0]["Login"].ToString();
				u.Pwd = dt.Rows[0]["Pwd"].ToString();
				u.Name = dt.Rows[0]["Name"].ToString();
				u.CreationTime= DateTime.Parse( dt.Rows[0]["CreationTime"].ToString() );
				u.LastLoginTime = DateTime.Parse( dt.Rows[0]["LastLoginTime"].ToString());		
				u.LastChangePasswordTime = DateTime.Parse( dt.Rows[0]["LastChangePasswordTime"].ToString());
				u.Ticket = dt.Rows[0]["Ticket"].ToString();
				u.TimeZone = TimeZoneHelper.GetTimeZone( int.Parse( dt.Rows[0]["TimeZone"].ToString() ) );
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

			return u;
		}
		

		public static bool CheckLogin( string userName, string pwd )
		{
			string sql = "select * from [User] where [Login]=@Login";
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;;
			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				DataTable dt = h.ExecuteDataTable( cmd );

				if( dt == null || dt.Rows.Count == 0)
					return false;

				string userPwd = dt.Rows[0]["Pwd"].ToString();
				return ( userPwd == pwd );
			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

		}

		public static bool UpdateLastLoginTime( string userName, DateTime t )
		{
			bool result = false;
			string sql = "update [User] set [LastLoginTime]=@LastLoginTime where [Login]=@Login";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@LastLoginTime", OleDbType.VarChar ).Value = t;
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;

			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;

			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

			return result;
		}


		public static bool UpdateLastChangePasswordTime( string userName, DateTime t )
		{
			bool result = false;
			string sql = "update [User] set [LastChangePasswordTime]=@LastChangePasswordTime where [Login]=@Login";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@LastChangePasswordTime", OleDbType.VarChar ).Value = t;
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;

			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;

			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

			return result;
		}

		public static bool UpdatePassword( string userName, string newpwd )
		{
			bool result = false;
			string sql = "update [User] set [Pwd]=@Pwd where [Login]=@Login";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Pwd", OleDbType.VarChar ).Value = newpwd;
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;

			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;

			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

			return result;
		}

		public static bool UpdateTicket( string userName, string ticket )
		{
			bool result = false;
			string sql = "update [User] set [Ticket]=@Ticket where [Login]=@Login";
			
			OleDbCommand cmd = new OleDbCommand( sql );
			cmd.Parameters.Add( "@Ticket", OleDbType.VarChar ).Value = ticket;
			cmd.Parameters.Add( "@Login", OleDbType.VarChar ).Value = userName;

			SqlHelper h = new SqlHelper();
			try
			{
				h.Open();
				result = h.ExecuteNonQuery( cmd ) > 0;

			}
			catch( Exception ex )
			{ 
				string s = ex.Message;
				throw; 
			}
			finally{ h.Close(); }

			return result;
		}
	}
}
